﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//------------//
using QLKTX1._0.DTO;
using QLKTX1._0.DB;
using System.Data.SqlClient;
using System.Data;
//....................
namespace QLKTX1._0.DAO
{
    class PhongDAO
    {
        public static List<PhongDTO> dsPhong()
        {
            List<PhongDTO> dsPhong = new List<PhongDTO>();

            ConnectDB con = new ConnectDB();

            string  sql = " SELECT dbo.Phong.maPhong, dbo.Khu.maKhu, dbo.Khu.tenKhu, dbo.Tang.tenTang, dbo.Tang.maTang, dbo.tenPhong.maTenPhong, dbo.tenPhong.soPhong, dbo.SoGiuong.maSG, dbo.SoGiuong.soLuong";
                    sql += " FROM dbo.Phong INNER JOIN";
                    sql += " dbo.Khu ON dbo.Phong.maKhu = dbo.Khu.maKhu INNER JOIN dbo.Tang ON dbo.Phong.maTang = dbo.Tang.maTang INNER JOIN";
                    sql += " dbo.tenPhong ON dbo.Phong.maTenPhong = dbo.tenPhong.maTenPhong INNER JOIN dbo.SoGiuong ON dbo.Phong.maSG_FK = dbo.SoGiuong.maSG"; 

            DataTable dt = con.getDataTable(sql);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                PhongDTO p = new PhongDTO();

                TenPhongDTO tp = new TenPhongDTO();
                tp.MaTenPhong  = Int32.Parse (dt.Rows[i]["maTenPhong"].ToString());
                tp.SoPhong = dt.Rows[i]["soPhong"].ToString();

                TangDTO t = new TangDTO();
                t.MaTang = dt.Rows[i]["maTang"].ToString();
                t.tenTang = dt.Rows[i]["tenTang"].ToString();

                KhuDTO k = new KhuDTO();
                k.MaKhu = dt.Rows[i]["maKhu"].ToString();
                k.TenKhu = dt.Rows[i]["tenKhu"].ToString();

                SoGiuongDTO sg = new SoGiuongDTO();
                sg.MaSG = dt.Rows[i]["maSG"].ToString();
                sg.SoLuong = Int32.Parse(dt.Rows[i]["soLuong"].ToString());

                p.MaPhong = dt.Rows[i]["maPhong"].ToString();
                p.MaTenPhong  = tp;
                p.MaTang  = t;
                p.MaKhu = k;
                p.MaSoGiuong = sg;

                dsPhong.Add(p );

            }
            return dsPhong;
        }
        //Ham Insert
        public static bool themPhong(PhongDTO p)
        {
            ConnectDB con = new ConnectDB();// dung DB

            return con.executeQuery("INSERT INTO Phong(maTenPhong,maTang,maKhu, maSG_FK) VALUES('" + p.MaTenPhong.MaTenPhong + "','" + p.MaTang.MaTang + "','" + p.MaKhu.MaKhu + "','" + p.MaSoGiuong .MaSG  + "')");
        }
        //Ham Delete
        public static bool xoaPhong(PhongDTO p)
        {
            ConnectDB con = new ConnectDB();

            return con.executeQuery("DELETE FROM Phong WHERE maPhong='"+p.MaPhong +"'and maTenPhong='" + p.MaTenPhong.MaTenPhong + "' and maTang='" + p.MaTang.MaTang + "' and maKhu='" + p.MaKhu.MaKhu + "' and maSG_FK='" + p.MaSoGiuong.MaSG + "'");
        }
        //Ham Update
        public static bool capNhatPhong(PhongDTO p)
        {
            ConnectDB con = new ConnectDB();

            return con.executeQuery("UPDATE Phong set maTenPhong='" + p.MaTenPhong.MaTenPhong + "', maTang='" + p.MaTang.MaTang + "' , maKhu='" + p.MaKhu.MaKhu + "' , maSG_FK='" + p.MaSoGiuong.MaSG + "' WHERE maPhong '"+p.MaPhong +"'");
        }

    }
}
